import pandas as pd
import json
from typing import List
import copy
from itertools import zip_longest

# 最终xlsx文件的字段
word_header = ['word_id', 'word', 'pos', 'lv', 'use_method', 'rate',
               'frequency_name', 'chinese', 'sentential_form', 'example', 'translation', 'notes']


def write_data_to_xlsx(data: List, columns, file_name):
    """
    写入文件
    :param data:
    :param columns:
    :param file_name:
    :return:
    """
    pd.DataFrame(data, columns=columns).to_excel(r'./parse_words/{0}.xlsx'.format(file_name),
                                                 sheet_name='Sheet1',
                                                 index=False,
                                                 header=True
                                                 )


def parse_gy_paraphrase(gy_paraphrase):
    # 解析数据
    gy_paraphrase_list = []
    for x in gy_paraphrase:
        frequency_name = x.get('frequency_name')
        chinese = x.get('chinese')
        gy_sentential_form = x.get('gy_sentential_form')  # 频
        gy_example = x.get('gy_example')  # 例子
        gy_notes = x.get('gy_notes')  # 笔记

        if gy_sentential_form:
            gy_sentential_form_example_list = [y.get('id') for c in gy_sentential_form for y in
                                               c.get('gy_example')]  # gy_sentential_form中所有的example 列表

            # 第一类
            for a in gy_sentential_form:
                gy_paraphrase_dic = {"frequency_name": frequency_name, "chinese": chinese}
                sentential_form = a.get('sentential_form') + '[SF{0}-{1}]'.format(len(gy_sentential_form),
                                                                                  gy_sentential_form.index(a) + 1)
                gy_example = a.get('gy_example')
                gy_notes = a.get('gy_notes')
                gy_paraphrase_dic.update({"sentential_form": sentential_form})

                for gy_exam, gy_note in zip_longest(gy_example, gy_notes):
                    if gy_exam:
                        example = gy_exam.get('english') + '[E{0}-{1}]'.format(len(gy_sentential_form_example_list),
                                                                               gy_sentential_form_example_list.index(
                                                                                   gy_exam.get('id')) + 1)
                        translation = gy_exam.get('chinese')
                    else:
                        example = ''
                        translation = ''
                    if gy_note:
                        notes = gy_note.get('notes')
                    else:
                        notes = ''
                    new_dic = copy.deepcopy(gy_paraphrase_dic)
                    new_dic.update({"example": example, "translation": translation, "notes": notes})
                    gy_paraphrase_list.append(new_dic)

        else:
            for gy_exam, gy_note in zip_longest(gy_example, gy_notes):
                dic = {"frequency_name": frequency_name, "chinese": chinese}
                if gy_exam:
                    example = gy_exam.get('english') + '[E{0}-{1}]'.format(len(gy_example),
                                                                           gy_example.index(gy_exam) + 1)
                    translation = gy_exam.get('chinese')
                else:
                    example = ''
                    translation = ''
                if gy_note:
                    notes = gy_note.get('notes')
                else:
                    notes = ''
                dic.update({"example": example, "translation": translation, "notes": notes})
                gy_paraphrase_list.append(dic)
    return gy_paraphrase_list


def parse_word_xlsx():
    # 解析主文件
    df = pd.read_excel(r'./split_table_data/word.xlsx', sheet_name='Sheet1', header=None)  # sheet_name 读取那个工作表
    data = df.values  # 全部数据
    header = data[0]  # 读取第一行的列名
    other_lines = data[1:]  # 除第一行外剩余数据
    result = [dict(zip(header, line)) for line in other_lines]
    xlsx_data = []  # 所有解析数据
    for i in result:
        word_id = i.get('id')
        word = i.get('word')
        pos = i.get('part_of_speech')

        detail_json = i.get('detail_json')
        if not isinstance(detail_json, float):  # 存在多条无内容数据 如:china
            json_data = json.loads(i.get('detail_json'))
            lv = ",".join(['{0}:{1}'.format(k, v) for k, v in json_data.items() if k.startswith('lv')])
            use_method = json_data.get('use_method')
            rate = json_data.get('rate')
            gy_paraphrase = json_data.get('gy_paraphrase')  # 主要解析
            gy_paraphrase_result = parse_gy_paraphrase(gy_paraphrase)
            for gy in gy_paraphrase_result:
                frequency_name = gy.get('frequency_name', '')
                chinese = gy.get('chinese', '')
                sentential_form = gy.get('sentential_form', '')
                example = gy.get('example', '')
                translation = gy.get('translation', '')
                notes = gy.get('notes', '')
                xlsx_data.append([
                    word_id, word, pos, lv, use_method, rate,
                    frequency_name, chinese, sentential_form, example, translation, notes
                ])
    write_data_to_xlsx(xlsx_data, word_header, 'parse_words')  # 写入数据到xlsx


if __name__ == '__main__':
    parse_word_xlsx()
